Rem
Rem $Header: dmhpdemo.sql 08-oct-2006.20:42:26 sylin Exp $
Rem
Rem dmhpdemo.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      dmhpdemo.sql - Demo for the Hierarchical Profiler package DBMS_HPROF
Rem
Rem    DESCRIPTION
Rem      dbmshpro_demo package demonstrates how to generate html reports from
Rem      dbms hierarchical profiler tables.
Rem
Rem    NOTES
Rem    The Hierarchical Profiler produces hierarchical profiler information
Rem    from the raw trace and stored them in the follwoing tables:
Rem      dbmshp_runs - information on hierarchical profiler runs.
Rem      dbmshp_function_info - information on each function profiled.
Rem      dbmshp_parent_child_info - parent-child level profiler information.
Rem
Rem    The analyze_reports procedures in dbmshpro_demo package produces
Rem    a collection of html reports that present information derived from the
Rem    raw profiler output stored in the hierarchical profiler tables.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    lvbcheng    09/18/06 - Add comments
Rem    sylin       04/11/05 - Created
Rem

/****************************************************************************
 *
 * The following example illustrates how to use dbms hierarchical profiler
 * package dbms_hprof to collect, analyze profile information, and then
 * use this dbmshpro_demo package to produce useful html reports.
 *
 * Before start collecting any hierarchical profiler information, use
 * dbmshptab.sql script in rdbms/admin directory to create the tables
 * required for persistently storing the hierarchical profiler data.
 *
 * Profiling program.
 *
 * Example:
 *
 * 1. Create directory object for hierarchical profiler.
 * - connect <someSchema> say SYS or someone else who can create
 * directory objects.
 * connect sys/<sysPassword> as sysdba;
 * - create directory "plshprof_dir" as '/home/dbmshpdemo/plshprof';
 * - grant read on directory "plshprof_dir" to dbmshpdemo;
 * - grant write on directory "plshprof_dir" to dbmshpdemo;
 *
 * 2. Profiling program
 * sqlplus dbmshpdemo/dbmshpdemo
 * execute dbms_hprof.start_profiling('plshprof_dir', 'test1.trc');
 * execute test;
 * execute dbms_hprof.stop_profiling;
 *
 * 3. Analyzing the raw trace data generated by step 2 and get the runid from
 *    the dbms_hprof.analyze run.
 * DECLARE
 *    runid number;
 * BEGIN 
 *   runid := dbms_hprof.analyze('plshprof_dir', 'test1.trc',
 *            run_comment =>'First run of test');
 * END;
 * /
 *
 * 4. Use dbmshpro_demo.analyze to generate useful html reports.
 *
 * DECLARE
 *   runid1 number;
 * BEGIN
 *   runid1 := dbms_hprof.analyze('plshprof_dir', 'test1.trc',
 *             run_comment =>'First run of test');
 *   dbmshpro_demo.analyze_reports('plshprof_dir', 'test1', runid);
 * END;
 * /
 *
 * 6. Modify test program to improve performance.
 *
 * 7. Repeat step 2 to get a new raw trace output file, test2.trc.
 *
 * 8. Repeat step 3 with the new raw trace output file from step 6.
 *
 * 9. Use dbmshpro_demo.analyze to generate useful html diff reports for
 *    the 2 runs.
 *
 * BEGIN
 *   dbmshpro_demo.analyze_reports(location, 'testdif', runid1, runid2);
 * END;
 * /
 ***************************************************************************/

CREATE OR REPLACE PACKAGE dbmshpro_demo AUTHID CURRENT_USER IS
  /* analyze_reports takes a location, a file name and a run_id
   * and generates a report based on data collected in the trace
   * file <fname> located at directory object <location> with run_id
   * <run_id>.
   *
   * ARGUMENTS:
   * location - a directory object that points to a directory on the
   * server file system where the trace file can be found.
   * fname - file name of the trace file.
   * run_id - target run id.
   */
  PROCEDURE analyze_reports(location      IN VARCHAR2,
                            fname         IN VARCHAR2,
                            run_id        IN NUMBER);

  /* This analyze_reports overload takes two run numbers to generate a diff
   * report of the two runs.
   */
  PROCEDURE analyze_reports(location      IN VARCHAR2,
                            fname         IN VARCHAR2,
                            run1_id       IN NUMBER,
                            run2_id       IN NUMBER);
END dbmshpro_demo;
/
show errors

CREATE OR REPLACE PACKAGE BODY dbmshpro_demo IS

  DBMSHP_SUBTREETIME         CONSTANT  PLS_INTEGER := 1;
  DBMSHP_FUNCTIONTIME        CONSTANT  PLS_INTEGER := 2;
  DBMSHP_CALLS               CONSTANT  PLS_INTEGER := 3;
  DBMSHP_NAME                CONSTANT  PLS_INTEGER := 4;
  DBMSHP_MEAN_SUBTREETIME    CONSTANT  PLS_INTEGER := 5;
  DBMSHP_MEAN_FUNCTIONTIME   CONSTANT  PLS_INTEGER := 6;
  DBMSHP_FILE_LINE_SIZE      CONSTANT  PLS_INTEGER := 32767;

  TYPE NumList  IS TABLE OF NUMBER(38,1);
  TYPE NumList2 IS TABLE OF NUMBER(38,0);
  TYPE NameList IS TABLE OF VARCHAR2(4000);
  TYPE RawList  IS TABLE OF RAW(32);
  CRLF          VARCHAR2(2 char) := '
';

  runid         NUMBER;
  runid2        NUMBER;
  namebuf       VARCHAR2(200);
  buf           VARCHAR2(32767) := '';
  myclob        CLOB;
  page_size     INTEGER;
  sql_stmt      VARCHAR2(4000);
  total_time    NUMBER;
  total_calls   NUMBER;
  filelocation  VARCHAR2(200);
  filename      VARCHAR2(200);
  fullname      VARCHAR2(300);
  time_mode     VARCHAR2(20);
  units         VARCHAR2(20) := ' (microsecs) ';
  perf_time1    NUMBER(38,0);
  perf_time2    NUMBER(38,0);

  /* Internal routine to write data to target file.
   * IMPLEMENTATION NOTES:
   * It uses two levels of buffering, one a string buffer,
   * a second one a clob. One arguably could use just the clob.
   */
  PROCEDURE write_data(data VARCHAR2, flush BOOLEAN) IS
  BEGIN

    IF (length(buf) + length(data) > 32767) THEN
      dbms_lob.writeappend(myclob, length(buf), buf);
      buf := data;
    ELSE
      buf := buf || data;
    END IF;

    IF (flush) THEN
      dbms_lob.writeappend(myclob, length(buf), buf);
      buf := '';
    END IF;
  END;

  PROCEDURE write_plstinfo IS
    Buffer        VARCHAR2(32767);
    Amount        BINARY_INTEGER := page_size;
    Position      INTEGER := 1;
    file_handle   UTL_FILE.FILE_TYPE;
  BEGIN

    -- Open file
    file_handle := UTL_FILE.FOPEN(filelocation, fullname, 'wb',
      DBMSHP_FILE_LINE_SIZE);

    BEGIN
      LOOP
        DBMS_LOB.READ(myclob, Amount, Position, Buffer);
        Position := Position + Amount;
        UTL_FILE.PUT_RAW(file_handle, UTL_RAW.CAST_TO_RAW(Buffer));
      END LOOP;

     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         dbms_output.put_line(fullname || ': *created*');
       WHEN OTHERS THEN
         dbms_output.put_line(fullname || ': *failed*');
    END;

    -- Reset myclob
    dbms_lob.trim(myclob, 0);

    -- Close file
    UTL_FILE.FCLOSE(file_handle);

    commit;
  END;

  /* Generate everything up to the title line of the HTML page body. */
  PROCEDURE report_title(title varchar2) IS
  BEGIN
    write_data(
      '<HTML>' || CRLF ||
      '<HEAD>' || CRLF ||
      '<TITLE>' || title || '</TITLE>' || CRLF ||
      '<style type="text/css">' || CRLF ||
      '<!--' || CRLF ||
      'td.left     { text-align : left }' || CRLF ||
      'a           { color : blue}' || CRLF ||
      'a.name_pls  { color : black} ' || CRLF ||
      'a.name_sql  { color : black} ' || CRLF ||
      'td          { text-align : right }' || CRLF ||
      'thead       { font-weight : bold ; color : blue}' || CRLF ||
      'td.num      { text-align : right }' || CRLF ||
      'td.bnum     { text-align : right ; font-weight : bold }' || CRLF ||
      'td.str      { text-align : left}' || CRLF ||
      'td.bstr     { text-align : left ; font-weight : bold }' || CRLF ||
      '-->' || CRLF ||
      '</style>' || CRLF ||
      '</HEAD>' || CRLF ||
      '<BODY>' || CRLF ||
      '<H1>' || title || '</H1>' || CRLF,
      FALSE);
  END;

  /* Construct namebuf with "<owner>.<module>.<function> (Line <line#>)" */
  PROCEDURE get_name(line  VARCHAR2, owner VARCHAR2, module VARCHAR2,
                     function VARCHAR2) IS

  BEGIN

    IF (line IS NULL OR line = 0) THEN
      IF (module IS NULL) THEN
        namebuf := SUBSTR(function,1,100);
      ELSE
        namebuf := SUBSTR
        (owner || '.' || module || '.' || function, 1, 100);
      END IF;
    ELSE
      IF (module IS NULL) THEN
        namebuf := SUBSTR(function || ' (Line '||line||')',1,100);
      ELSE
        namebuf := SUBSTR
        (owner||'.'||module||'.'||function||' (Line '||line||')',1,100);
      END IF;
    END IF;
  END;

  /* Generate PL/SQL Time Analysis single run start page */
  PROCEDURE single_run_start_page IS
  BEGIN

    write_data(
      '<HTML>' || CRLF ||
      '<HEAD><TITLE>PL/SQL ' || time_mode || ' Analysis for ' || filename ||
      '</TITLE></HEAD>' || CRLF ||
      '<BODY>' || CRLF || 
      '<H1>PL/SQL ' || time_mode || ' Analysis for ' || filename ||
      '</H1>' || CRLF ||
      '<H3>' || total_time || units || time_mode || ' and '|| total_calls ||
      ' function calls</H3>' || CRLF ||
      '<H4>The Hierarchical Profiler produces a collection of ' || CRLF ||
      'reports that present information derived from the raw ' || CRLF ||
      'profiler output in a variety of formats. The following ' || CRLF ||
      'reports have been found to be the most generally useful as ' || CRLF ||
      'starting points for browsing:</H4>' || CRLF ||
      '<H4>' || time_mode || units || 'Summary Reports:</H4>' || CRLF ||
      '<UL>' || CRLF ||
      '<LI><A HREF="' || filename || '_subtree.html">' ||
      'Sorted by Total Subtree ' || time_mode || '</A></LI>' || CRLF ||
      '<LI><A HREF="' || filename || '_function.html">' ||
      'Sorted by Total Function ' || time_mode || '</A></LI>' || CRLF ||
      '<LI><A HREF="' || filename || '_calls.html">' ||
      'Sorted by Total Function Call Count</A></LI>' || CRLF ||
      '<LI><A HREF="' || filename || '_name.html">' ||
      'Sorted by Function Name</A></LI>'  || CRLF ||
      '<br/><LI><A HREF="' || filename || '_mean_subtree.html">' ||
      'Sorted by Mean Subtree ' || time_mode || '</A></LI>'  || CRLF ||
      '<LI><A HREF=" ' || filename || '_mean_function.html">' ||
      'Sorted by Mean Function ' || time_mode || '</A></LI>' || CRLF ||
      '<br/><LI><A HREF="' || filename || '_namespace.html">' ||
      'Group by Namespace' || '</A></LI>'  || CRLF ||
      '<LI><A HREF="' || filename || '_module_function.html">' ||
      'Group by Module Name: Sorted by Total Function ' || time_mode ||
      '</A></LI>' || CRLF ||
      '<LI><A HREF="' || filename || '_module_calls.html">' ||
      'Group by Module Name: Sorted by Total Function Call Count' ||
      '</A></LI>'  || CRLF ||
      '<LI><A HREF="' || filename || '_module_name.html">' ||
      'Group by Module Name: Sorted by Module Name'||'</A></LI>'|| CRLF ||
      '</UL>' || CRLF ||
      '<H4>In addition, the following reports are also available:' ||
      '</H4>' || CRLF ||
      '<UL>' || CRLF ||
      '<LI><A HREF="' || filename || '_parent_child.html">' ||
      'Parents and Children ' || time_mode || ' Data</A></LI>' || CRLF ||
      '</UL>' || CRLF || '</BODY>' || CRLF || '</HTML>',
      TRUE);

    write_plstinfo;
  END;

  /* Generate start page of a difference mode report using 
   * runid and runid2.
   * Returns FALSE if the two runids have the same total run time.
   */
  FUNCTION diff_mode_start_page RETURN BOOLEAN IS
    fsum1        NUMBER(38,0);
    fsum2        NUMBER(38,0);
    timediff     NUMBER(38,0);
    percent      NUMBER(38,0);
    perform      VARCHAR2(15) := '';
    fcount       PLS_INTEGER;
  BEGIN

    report_title('PL/SQL ' || time_mode || units || 'Analysis - Summary Page');

    sql_stmt :=
      'SELECT SUM(function_elapsed_time) FROM dbmshp_function_info ' ||
      'WHERE runid = :b1';
    EXECUTE IMMEDIATE sql_stmt INTO fsum1 USING runid;
    EXECUTE IMMEDIATE sql_stmt INTO fsum2 USING runid2;

    IF (fsum1 = fsum2) THEN
      write_data('<H2><B>No Differences Encountered</B></H2>', TRUE);
      write_plstinfo;
      return FALSE;
    END IF;

    timediff := ABS(fsum2 - fsum1);
    percent := timediff/fsum1*100;

    IF (fsum2 > fsum1) THEN
      perform := 'regression';
    ELSIF  (fsum2 < fsum1) THEN
      perform := 'improvement';
    END IF;

    -- dbmshp_t1 view contains function level profiler information from
    -- dbmshp_function_info table for a particular run specified by runid.
    sql_stmt :=
    'CREATE OR REPLACE VIEW dbmshp_t1 AS ' ||
    'SELECT calls, function_elapsed_time ftime, symbolid sid, ' ||
    'SUBSTR(decode(owner, null, '''', owner||''.'')||' ||
    'decode(module,null, '''', module),1,100) mname, ' ||
    'SUBSTR(decode(owner, null, '''', owner||''.'')||' ||
    'decode(module, null, '''', module||''.'')||' ||
    'decode(function,null, '''', function),1,100) name, ' ||
    'hash, line#, namespace, subtree_elapsed_time stime ' ||
    'FROM dbmshp_function_info WHERE runid = ' || runid;

    EXECUTE IMMEDIATE sql_stmt;

    -- dbmshp_t2 view contains function level profiler information from
    -- dbmshp_function_info table for a particular run specified by runid2.
    sql_stmt :=
    'CREATE OR REPLACE VIEW dbmshp_t2 AS ' ||
    'SELECT calls, function_elapsed_time ftime, symbolid sid, ' ||
    'SUBSTR(decode(owner, null, '''', owner||''.'')||' ||
    'decode(module,null, '''', module),1,100) mname, ' ||
    'SUBSTR(decode(owner, null, '''', owner||''.'')||' ||
    'decode(module, null, '''', module||''.'')||' ||
    'decode(function,null, '''', function),1,100) name, ' ||
    'hash, line#, namespace, subtree_elapsed_time stime ' ||
    'FROM dbmshp_function_info WHERE runid = ' || runid2;

    EXECUTE IMMEDIATE sql_stmt;

    -- dbmshp_diftab view contains function level profiler differences
    -- information from dbmshp_t1 and dbmshp_t2 views.
    sql_stmt :=
      'CREATE OR REPLACE VIEW dbmshp_diftab AS SELECT ' ||
      'dbmshp_t1.name, dbmshp_t1.sid sid, dbmshp_t1.calls c1, ' ||
      'dbmshp_t2.calls c2, dbmshp_t1.ftime r1, dbmshp_t2.ftime r2, ' ||
      'dbmshp_t1.stime s1, dbmshp_t2.stime s2, ' ||
      '(dbmshp_t1.stime-dbmshp_t1.ftime) d1, ' ||
      '(dbmshp_t2.stime-dbmshp_t2.ftime) d2, ' ||
      '(dbmshp_t2.calls-dbmshp_t1.calls) calls, ' ||
      '(dbmshp_t2.calls-dbmshp_t1.calls)/dbmshp_t1.calls*100 callsrels, ' ||
      'dbmshp_t1.namespace, dbmshp_t1.mname mname, ' ||
      'dbmshp_t1.name||decode(dbmshp_t1.line#,0,'''',' ||
      '''(Line ''||dbmshp_t1.line#||'')'') names, ' ||
      'dbmshp_t1.hash, (dbmshp_t2.ftime-dbmshp_t1.ftime) ftime, ' ||
      '(dbmshp_t2.ftime-dbmshp_t1.ftime)/dbmshp_t1.ftime*100 ftimerel, ' ||
      '(dbmshp_t2.ftime/dbmshp_t2.calls)-' ||
      '(dbmshp_t1.ftime/dbmshp_t1.calls) mftime, ' ||
      '((dbmshp_t2.ftime/dbmshp_t2.calls)-' ||
      '(dbmshp_t1.ftime/dbmshp_t1.calls))/' ||
      '(dbmshp_t1.ftime/dbmshp_t1.calls)*100 mftimerel, ' ||
      '(dbmshp_t2.stime-dbmshp_t1.stime) stime ' ||
      'FROM dbmshp_t1, dbmshp_t2  WHERE ' ||
      '(substr(dbmshp_t1.name||dbmshp_t1.hash,1,100) = ' ||
      'substr(dbmshp_t2.name||dbmshp_t2.hash,1,100))' ||
      ' AND (dbmshp_t1.namespace = dbmshp_t2.namespace) AND ' ||
      '(dbmshp_t1.ftime <> 0) UNION SELECT ' ||
      'name, sid, calls c1, calls c2, ' ||
      'ftime r1, ftime r2, stime s1, stime s2, ' ||
      '(stime-ftime) d1, (stime-ftime) d2, ' ||
      '0, 0 callsrels, namespace, ' ||
      'mname, ' ||
      'name||decode(line#,0,'''',''(Line ''||line#||'')'') names, ' ||
      'hash, 0, 0 ftimerel, 0 mftime, 0 mftimerel, 0 ' ||
      'FROM dbmshp_t1 ' ||
      'WHERE  ftime = 0 ' ||
      'UNION SELECT ' ||
      'name, sid, 0 c1, calls c2, ' ||
      '0 r1, ftime r2, 0 s1, stime s2, ' ||
      '0 d1, (stime-ftime) d2, ' ||
      'calls, 0 callsrels, namespace, ' ||
      'mname, ' ||
      'name||decode(line#,0,'''',''(Line ''||line#||'')'') names, ' ||
      'hash, ftime, 0 ftimerel, 0 mftime, 0 mftimerel, stime ' ||
      'FROM dbmshp_t2 ' ||
      'WHERE name NOT IN (SELECT name from dbmshp_t1) ' ||
      'UNION SELECT ' ||
      'name, sid, calls c1, 0 c2, ' ||
      'ftime r1, 0 r2, stime s1, 0 s2, ' ||
      '(stime-ftime) d1, 0 d2, ' ||
      '-calls, 0 callsrels, namespace, ' ||
      'mname, ' ||
      'name||''(Line ''||line#||'')'' names, ' ||
      'hash, -ftime, 0 ftimerel, 0 mftime, 0 mftimerel, -stime ' ||
      'FROM dbmshp_t1 ' ||
      'WHERE name NOT IN (SELECT name from dbmshp_t2)';

    EXECUTE IMMEDIATE sql_stmt;

    write_data(
      'This analysis finds a net <B>' || perform || '</B> of ' ||
      '<B>' || timediff || '</B> microsecs (' || time_mode || ') or <B>' ||
      percent || '%</B> (<B>' || fsum1 || '</B> versus <B>' ||
      fsum2 || '</B>).<BR>' || CRLF,
      FALSE);

    sql_stmt :=
      'SELECT COUNT(*) FROM dbmshp_diftab';
    EXECUTE IMMEDIATE sql_stmt INTO fcount;

    write_data(
      'Here is a summary of the ' || fcount || ' most important ' ||
      'individual function regressions and improvements.' || CRLF ||
      '<H3>Function Level Difference Reports:</H3>' || CRLF ||
      '<UL>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_fr.html">' || CRLF ||
      'Function with Performance Regressions: ' || 
      'Sorted by Function Time Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_fi.html">' || CRLF ||
      'Function with Performance Improvements: ' || 
      'Sorted by Function Time Delta</A></LI>' || CRLF ||
      '<br/><LI><A HREF =" ' || filename || '_ns.html">' || CRLF ||
      'Difference data for all functions:  ' || 
      'Sorted by Total Subtree ' || time_mode || units ||
      'Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_nf.html">' || CRLF ||
      'Difference data for all functions:  ' || 
      'Sorted by Total Function ' || time_mode || units ||
      'Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_nc.html">' || CRLF ||
      'Difference data for all functions:  ' || 
      'Sorted by Total Function Call Count Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_nn.html">' || CRLF ||
      'Difference data for all functions:  ' || 
      'Sorted by Function Name</A></LI>' || CRLF ||
      '</UL>' || CRLF || '<H3>' ||
      'In addition, the following reports are also available:</H3>' || CRLF ||
      '<UL>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_nsp.html">' || CRLF ||
      'Group by Namespace</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_2f.html">' || CRLF ||
      'Group by Module Name: ' ||
      'Sorted by Total Module Time Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_2c.html">' || CRLF ||
      'Group by Module Name: ' ||
      'Sorted by Total Module Call Count Delta</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_2n.html">' || CRLF ||
      'Group by Module Name: ' ||
      'Sorted by Total Module Name</A></LI>' || CRLF ||
      '<LI><A HREF =" ' || filename || '_pc.html">' || CRLF ||
      'Parents and Children Difference Report for all functions</A>' || CRLF ||
      '</LI>' || CRLF ||
      '</UL>' || CRLF || '</BODY>' || CRLF || '</HTML>',
      TRUE);

    write_plstinfo;
    RETURN TRUE;
  END;

  /* Generate function level summary reports sorted on a particular
   * attribute.
   */
  PROCEDURE function_level_report(ordertype PLS_INTEGER,
                                  mean_report BOOLEAN) IS
    orderclause VARCHAR2(100);
    sortedby    VARCHAR2(100);
    bstime      VARCHAR2(30) := '<td class="num">';
    bftime      VARCHAR2(30) := '<td class="num">';
    bmstime     VARCHAR2(30) := '<td class="num">';
    bmftime     VARCHAR2(30) := '<td class="num">';
    bcalls      VARCHAR2(30) := '<td class="num">';
    bname       VARCHAR2(30) := '<td class="str">';
    mstimes     NumList2;
    mftimes     NumList2;
    stimes      NumList;
    ftimes      NumList;
    dtimes      NumList;
    calls       NumList;
    owners      NameList;
    modules     NameList;
    fnames      NameList;
    lines       NumList;
    hashtab     RawList;
    stimeind    NUMBER(38,1);
    ftimeind    NUMBER(38,1);
    dtimeind    NUMBER(38,1);
    callsind    NUMBER(38,1);

  BEGIN

    IF (ordertype = DBMSHP_SUBTREETIME) THEN
      orderclause := 'subtree_elapsed_time desc';
      sortedby := 'Total Subtree Time';
      bstime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_FUNCTIONTIME) THEN
      orderclause := 'function_elapsed_time desc';
      sortedby := 'Total Function Time';
      bftime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_CALLS) THEN
      orderclause := 'calls desc';
      sortedby := 'Total Number of Calls';
      bcalls := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_NAME) THEN
      orderclause := 
        'SUBSTR(owner||''.''||module||''.''||function, 1, 100) asc';
      sortedby := 'Name';
      bname := '<td class="bstr">';
    ELSIF (ordertype = DBMSHP_MEAN_SUBTREETIME) THEN
      orderclause := '(subtree_elapsed_time/calls) desc';
      sortedby := 'Mean Subtree Time';
      bmstime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_MEAN_FUNCTIONTIME) THEN
      orderclause := '(function_elapsed_time/calls) desc';
      sortedby := 'Mean Function Time';
      bmftime := '<td class="bnum">';
    END IF;

    report_title(time_mode || ' Report' || units || 'Sorted By ' || sortedby);

    write_data(
      '<table border="1">' || CRLF || '<thead>' || CRLF,
      FALSE);

    IF (mean_report = TRUE) THEN
      write_data(
        '<td>Mean Subtree Time</td>' || CRLF ||
        '<td>Mean Function Time</td>' || CRLF, FALSE);
    END IF;

    write_data(
      '<td>Subtree Time</td><td>Ind%</td>' || CRLF ||
      '<td>Function Time</td><td>Ind%</td>' || CRLF ||
      '<td>Descendants Time</td><td>Ind%</td>' || CRLF ||
      '<td>Calls</td><td>Ind%</td>' || CRLF ||
      '<td class="left">Function Name</td>' || CRLF ||
      '</thead>' || CRLF, FALSE);

    sql_stmt := 'SELECT subtree_elapsed_time, function_elapsed_time, ' ||
      '(subtree_elapsed_time-function_elapsed_time), ' ||
      'calls, owner, module, function, line#, hash, ' ||
      '(subtree_elapsed_time/calls), (function_elapsed_time/calls) ' ||
      'FROM dbmshp_function_info ' ||
      'WHERE runid = :b1 ' ||
      ' ORDER BY ' || orderclause;
    
    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      stimes, ftimes, dtimes, calls, owners, modules, fnames,
      lines, hashtab, mstimes, mftimes
      USING runid;

    FOR i in stimes.FIRST..stimes.LAST LOOP

      get_name(lines(i), owners(i), modules(i), fnames(i));

      stimeind := stimes(i)/total_time*100;
      ftimeind := ftimes(i)/total_time*100;
      dtimeind := dtimes(i)/total_time*100;
      callsind := calls(i)/total_calls*100;

      write_data('<tr>' || CRLF, FALSE);

      IF (mean_report = TRUE) THEN
        write_data(
          bmstime || mstimes(i) || '</td>' || CRLF ||
          bmftime || mftimes(i) || '</td>' || CRLF, FALSE);
      END IF;

      write_data(
        bstime || stimes(i) || '</td>' || CRLF ||
        bstime || stimeind  || '%</td>' || CRLF ||
        bftime || ftimes(i) || '</td>' || CRLF ||
        bftime || ftimeind  || '%</td>' || CRLF ||
        '<td class="num">'  || dtimes(i) || '</td>' || CRLF ||
        '<td class="num">'  || dtimeind || '%</td>' || CRLF ||
        bcalls || calls(i)  || '</td>' || CRLF ||
        bcalls || callsind  || '%</td>' || CRLF ||
        bname  || '<a class="name_pls"' || 'HREF="' || filename ||
        '_parent_child.html#' || namebuf || '#' || hashtab(i) ||
        '">' || namebuf || '</A></td>' || CRLF ||
        '</tr>' || CRLF, FALSE);
    END LOOP;

    write_data(CRLF || '</TABLE>' || CRLF || '</BODY>' || CRLF || '</HTML>',
               TRUE);

    write_plstinfo;
  END;

  /* Generate namespace level summary report */
  PROCEDURE namespace_report IS
    names       NameList;
    ftimes      NumList;
    calls       Numlist;
    ftimeind    NUMBER(38,1);
    callsind    NUMBER(38,1);
  BEGIN

    report_title(time_mode || ' Report' || units || ': Grouped By Namespace');

    write_data(
      '<table border="1">' || CRLF ||
      '<thead style="color:brown">' || CRLF ||
      '<td>Namespace</td>' || CRLF ||
      '<td>Function Time</td>' || CRLF ||
      '<td class="left">Ind%</td>' || CRLF ||
      '<td>Calls</td>' || CRLF ||
      '<td class="left">Ind%</td>' || CRLF ||
      '</thead>' || CRLF, FALSE);

    sql_stmt := 'SELECT namespace, sum(function_elapsed_time), sum(calls) ' ||
      'FROM dbmshp_function_info ' ||
      'WHERE runid = :b1 group by namespace';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      names, ftimes, calls USING runid;

    FOR i in ftimes.first..ftimes.last LOOP
      ftimeind := ftimes(i)/total_time*100;
      callsind := calls(i)/total_calls*100;

      write_data(
        '<tr>' || CRLF ||
        '<td class="left">'  || names(i) || '</td>' || CRLF ||
        '<td>'  || ftimes(i) || '</td>'  || CRLF ||
        '<td>'  || ftimeind  || '%</td>' || CRLF ||
        '<td>'  || calls(i)  || '</td>'  || CRLF ||
        '<td>'  || callsind  || '%</td>' || CRLF ||
        '</tr>' || CRLF, FALSE);
    END LOOP;

    write_data('</TABLE>' || CRLF || '</BODY>' || CRLF || '</HTML>', TRUE);

    write_plstinfo;
  END;

  /* Generate parents and children report */
  PROCEDURE parent_child_report IS
    stimes      NumList;
    ftimes      NumList;
    dtimes      NumList;
    calls       NumList;
    owners      NameList;
    modules     NameList;
    fnames      NameList;
    lines       NumList;
    hashtab     RawList;
    pstimes     NumList;
    pftimes     NumList;
    pcalls      NumList;
    sids        NumList;
    pids        NumList;
    x           PLS_INTEGER;
    dtime       NUMBER;
    stimeind    NUMBER(38,1);
    ftimeind    NUMBER(38,1);
    dtimeind    NUMBER(38,1);
    callsind    NUMBER(38,1);

  BEGIN

    report_title('Parents and Children Report with ' ||
                 time_mode || units);

    sql_stmt := 'SELECT subtree_elapsed_time, function_elapsed_time, ' ||
      'calls, owner, module, function, line#, hash, symbolid, ' ||
      '(subtree_elapsed_time-function_elapsed_time) ' ||
      'FROM dbmshp_function_info WHERE runid = :b1 ' ||
      'ORDER BY symbolid';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      stimes, ftimes, calls, owners, modules, fnames, lines, hashtab,
      sids, dtimes USING runid;

    FOR i in 1..sids.COUNT LOOP
      get_name(lines(i), owners(i), modules(i), fnames(i));

      stimeind  := stimes(i)/total_time*100;
      ftimeind  := ftimes(i)/total_time*100;
      dtimeind  := dtimes(i)/total_time*100;
      callsind  := calls(i)/total_calls*100;

    -- Output name
      write_data('<b><a name="' || namebuf || '#' || hashtab(i) ||
                 '">' || namebuf || '</A></b>' || CRLF, FALSE);

      -- Output table
      write_data(
        '<table border="1">' || CRLF ||
        '<thead>' || CRLF ||
        '  <td>Subtree Time</td>' || CRLF ||
        '  <td>Ind%</td>' || CRLF ||
        '  <td>Function Time</td>' || CRLF ||
        '  <td>Ind%</td>' || CRLF ||
        '  <td>Descendants Time</td>' || CRLF ||
        '  <td>Ind%</td>' || CRLF ||
        '  <td>Calls</td>' || CRLF ||
        '  <td>Ind%</td>' || CRLF ||
        '  <td class="left">Function Name</td>' || CRLF ||
        '</thead>'|| CRLF ||
        '<tr>' || CRLF ||
        ' <td>' || stimes(i) || '</td>' || CRLF ||
        ' <td>' || stimeind  || '%</td>' || CRLF ||
        ' <td>' || ftimes(i) || '</td>' || CRLF ||
        ' <td>' || ftimeind  || '%</td>' || CRLF ||
        ' <td>' || dtimes(i) || '</td>' || CRLF ||
        ' <td>' || dtimeind  || '%</td>' || CRLF ||
        ' <td>' || calls(i)  || '</td>' || CRLF ||
        ' <td>' || callsind  || '%</td>' || CRLF ||
        ' <td class="left">' || CRLF ||
        '   <A HREF="'  || filename || '_parent_child.html#' || namebuf ||
        '#' || hashtab(i) || '">' || namebuf || '</A>' || CRLF ||
        ' </td>' || CRLF || '</tr>' || CRLF, FALSE);

      -- output Parent info.
      sql_stmt := 'SELECT parentsymid, ' ||
        'subtree_elapsed_time, function_elapsed_time, calls ' ||
        'FROM dbmshp_parent_child_info WHERE runid = :b1 AND ' ||
        'childsymid = :b2 ORDER BY subtree_elapsed_time desc';

      EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
        pids, pstimes, pftimes, pcalls USING runid, sids(i);

      write_data(
        '<tr>' || CRLF ||
        ' <td>' || CRLF ||
        '   <b>Parents:</b>' || CRLF ||
        ' </td>' || CRLF ||
        '</tr>' || CRLF, FALSE);

      -- <ROOT> entry
      IF (pids.COUNT = 0) THEN
        write_data(
          '<tr>' || CRLF ||
          ' <td>' || stimes(i) || '</td>' || CRLF ||
          ' <td>100%</td>' || CRLF ||
          ' <td>' || ftimes(i) || '</td>' || CRLF ||
          ' <td>100%</td>' || CRLF ||
          ' <td>' || dtimes(i) || '</td>' || CRLF ||
          ' <td>100%</td>' || CRLF ||
          ' <td>' || calls(i)  || '</td>' || CRLF ||
          ' <td>100%</td>' || CRLF ||
          ' <td class="left">' || CRLF ||
          '   <A HREF="' || filename ||
          '_parent_child.html#root">root</A>' || CRLF ||
          ' </td>' || CRLF ||
          '</tr>' || CRLF, FALSE);
      END IF;

      FOR j in 1..pids.COUNT LOOP
        x := pids(j);

        get_name(lines(x), owners(x), modules(x), fnames(x));

        dtime := pstimes(j)-pftimes(j);

        IF stimes(i) = 0 THEN
          stimeind := 0;
        ELSE
          stimeind := pstimes(j)/stimes(i)*100;
        END IF;
        IF ftimes(i) = 0 THEN
          ftimeind := 0;
        ELSE
          ftimeind := pftimes(j)/ftimes(i)*100;
        END IF;
        IF dtimes(i) = 0 THEN
          dtimeind := 0;
        ELSE
          dtimeind := dtime/dtimes(i)*100;
        END IF;
        callsind := pcalls(j)/calls(i)*100;

        write_data(
          '<tr>' || CRLF ||
          '<td>' || pstimes(j) || '</td>' || CRLF ||
          '<td>' || stimeind  || '%</td>' || CRLF ||
          '<td>' || pftimes(j) || '</td>' || CRLF ||
          '<td>' || ftimeind  || '%</td>' || CRLF ||
          '<td>' || dtime     || '</td>' || CRLF ||
          '<td>' || dtimeind  || '%</td>' || CRLF ||
          '<td>' || pcalls(j)  || '</td>' || CRLF ||
          '<td>' || callsind  || '%</td>' || CRLF ||
          '<td class="left">' || CRLF ||
          '  <A HREF="' || filename || '_parent_child.html#' ||
          namebuf || '#' || hashtab(x) || '">' || namebuf ||
          '</A></td>' || CRLF || '</tr>' || CRLF, FALSE);
      END LOOP;

      -- output children info.
      sql_stmt := 'SELECT childsymid, subtree_elapsed_time, ' ||
        'function_elapsed_time, calls FROM dbmshp_parent_child_info ' ||
        'WHERE runid = :b1 AND parentsymid = :b2 ' ||
        'ORDER BY subtree_elapsed_time desc';
      EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO pids, pstimes,
        pftimes, pcalls USING runid, sids(i);

      IF pids.COUNT > 0 THEN
        write_data('<tr><td><b>Children:</b></td></tr>' || CRLF, FALSE);
      END IF;

      FOR j in 1..pids.COUNT LOOP
        x := pids(j);
        get_name(lines(x), owners(x), modules(x), fnames(x));

        dtime := pstimes(j)-pftimes(j);

        IF dtimes(i) = 0 THEN
          stimeind := 0; 
        ELSE
          stimeind := pstimes(j)/dtimes(i)*100;
        END IF;
        IF ftimes(x) = 0 THEN
          ftimeind := 0; 
        ELSE
          ftimeind := pftimes(j)/ftimes(x)*100;
        END IF;
        IF dtimes(x) = 0 THEN
          dtimeind := 0;
        ELSE
          dtimeind := dtime/dtimes(x)*100;
        END IF;
        callsind := pcalls(j)/calls(x)*100;

        write_data(
          '<tr>' || CRLF ||
          '<td>' || pstimes(j) || '</td>' || CRLF ||
          '<td>' || stimeind  || '%</td>' || CRLF ||
          '<td>' || pftimes(j) || '</td>' || CRLF ||
          '<td>' || ftimeind  || '%</td>' || CRLF ||
          '<td>' || dtime     || '</td>' || CRLF ||
          '<td>' || dtimeind  || '%</td>' || CRLF ||
          '<td>' || pcalls(j)  || '</td>' || CRLF ||
          '<td>' || callsind  || '%</td>' || CRLF ||
          '<td class="left">' || CRLF ||
          '  <A HREF="' || filename || '_parent_child.html#' || namebuf ||
          '#' || hashtab(x) || '">' || namebuf || '</A></td>' || CRLF ||
          '</tr>' || CRLF, FALSE);
      END LOOP;

      write_data('</table><br/>'|| CRLF, FALSE);
    END LOOP;

    write_data('</BODY>' || CRLF || '</HTML>', TRUE);

    write_plstinfo;
  END;

  /* Generate module level summary reports */
  PROCEDURE module_report(ordertype PLS_INTEGER) IS
    orderclause VARCHAR2(100);
    sortedby    VARCHAR2(100);
    bftime      VARCHAR2(30) := '<td class="num">';
    bcalls      VARCHAR2(30) := '<td class="num">';
    bname       VARCHAR2(30) := '<td class="str">';
    ftimes      NumList;
    calls       NumList;
    fnames      NameList;
    owners      NameList;
    modules     NameList;
    ftimeind    NUMBER(38,1);
    callsind    NUMBER(38,1);

  BEGIN

    IF (ordertype = DBMSHP_FUNCTIONTIME) THEN
      orderclause := 'sum(function_elapsed_time) desc';
      sortedby := '(Total Module Time)';
      bftime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_CALLS) THEN
      orderclause := 'sum(calls) desc';
      sortedby := '(Total Number of Calls)';
      bcalls := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_NAME) THEN
      orderclause := 'substr(owner || ''.'' || module, 1, 100) asc';
      sortedby := '(Module Name)';
      bname := '<td class="bstr">';
    END IF;

    report_title(time_mode || ' Report:' || units ||
                 'Group by Module Name Sorted By ' || sortedby);

    write_data(
      '<table border="1">' || CRLF ||
      '<thead>' || CRLF ||
      '<td>Module Time</td><td>Ind%</td>' || CRLF ||
      '<td>Calls</td><td>Ind%</td>' || CRLF ||
      '<td class="left">Module Name</td>' || CRLF ||
      '</thead>' || CRLF, FALSE);

    sql_stmt := 'SELECT sum(function_elapsed_time), sum(calls), ' ||
      'SUBSTR(owner||''.''||module, 1, 50) FROM dbmshp_function_info ' ||
      'WHERE runid = :b1 ' ||
      'GROUP BY module, owner ' ||
      ' ORDER BY ' || orderclause;

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      ftimes, calls, fnames USING runid;

    FOR i in ftimes.first..ftimes.last LOOP
      ftimeind := ftimes(i)/total_time*100;
      callsind := calls(i)/total_calls*100;

      IF (fnames(i) = '.') THEN
        fnames(i) := '';
      END IF;

      write_data(
        '<tr>' || CRLF ||
        bftime || ftimes(i) || '</td>' || CRLF ||
        bftime || ftimeind || '%</td>' || CRLF ||
        bcalls || calls(i) || '</td>' || CRLF ||
        bcalls || callsind || '%</td>' || CRLF ||
        bname  || fnames(i)  || '</td>' || CRLF ||
        '</tr>' || CRLF, FALSE);
    END LOOP;
    
    write_data('</table>' || CRLF || '</BODY>' || CRLF || '</HTML>', TRUE);
    
    write_plstinfo;

  END;

  /* Generate functions with performance improvements/regressions report */
  PROCEDURE performance_report(where_clause VARCHAR2, pmode VARCHAR2) IS
    cumind    NUMBER(38,1) := 0;
    perf_time NUMBER(38,0);
    ftimeind  NUMBER(38,1);
    stimes    NumList;
    ftimes    NumList;
    dtimes    NumList;
    calls     NumList;
    callrels  NumList;
    fnames    NameList;
    ftimerels NumList;
    mftimes   NumList2;
    mtimerels NumList;
    hashtab   RawList;

  BEGIN

    sql_stmt := 'SELECT SUM(ftime) FROM dbmshp_diftab WHERE ' || where_clause;
    EXECUTE IMMEDIATE sql_stmt INTO perf_time;

    sql_stmt :=
      'SELECT calls, callsrels, names, hash, ftime, stime, (stime-ftime), ' ||
      'ftimerel, mftime, mftimerel FROM dbmshp_diftab WHERE ' ||
      where_clause || ' ORDER BY ftime desc';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      calls, callrels, fnames, hashtab, ftimes, stimes, dtimes, ftimerels,
      mftimes, mtimerels;

    write_data(
      '<HTML><HEAD>' || CRLF ||
      '<TITLE>Function with Performance ' || pmode ||
      ': Sorted by Function Time' || units || 'Delta</TITLE>' || CRLF ||
      '</HEAD><BODY>' || CRLF ||
      '<H1>Function ' || time_mode || units || 'Data for Performance ' ||
      pmode || '</H1>' || CRLF ||
      '<B>Total ' || pmode || ': ' || abs(perf_time) || ' microsecs ' ||
      time_mode || '</B>' || CRLF ||
      '<PRE><TABLE BORDER=1>' || CRLF ||
      '<TR ALIGN=RIGHT>' ||
      '<TD>Subtree Time</TD>' ||
      '<TD>Function Time Delta</TD>' ||
      '<TD>Rel%</TD>' ||
      '<TD>Ind%</TD>' ||
      '<TD>Cum%</TD>' ||
      '<TD>Descendants Time Delta</TD>' ||
      '<TD>Calls Delta</TD>' ||
      '<TD>Rel%</TD>' ||
      '<TD>Mean Function Time Delta</TD>' ||
      '<TD>Rel%</TD>' ||
      '<TD ALIGN=LEFT>Function Name</TD></TR>' || CRLF,
      FALSE);

    FOR i in 1..fnames.COUNT LOOP
      ftimeind := ftimes(i)/perf_time*100;
      cumind := cumind + ftimeind;
      IF (cumind > 100) THEN
        cumind := 100;
      END IF;
      write_data(
        '<TR ALIGN=RIGHT>' || CRLF ||
        '<TD>'    || stimes(i)    || '</TD>' || CRLF ||
        '<TD><B>' || ftimes(i)    || '</B></TD>' || CRLF ||
        '<TD>'    || (CASE ftimerels(i) WHEN 0 THEN '</TD>'
          ELSE '<B>' || ftimerels(i) || '%</B></TD>' END) || CRLF ||
        '<TD><B>' || ftimeind     || '%</B></TD>' || CRLF ||
        '<TD>'    || cumind       || '%</TD>' || CRLF ||
        '<TD>'    || dtimes(i)    || '</TD>' || CRLF ||
        '<TD>'    || calls(i)     || '</TD>' || CRLF ||
        '<TD>'    || (CASE callrels(i) WHEN 0 THEN '</TD>'
          ELSE callrels(i) || '%</TD>' END) || CRLF ||
        '<TD>'    || (CASE mftimes(i) WHEN 0 THEN '</TD>'
          ELSE mftimes(i) || '</TD>' END) || CRLF ||
        '<TD>'    || (CASE mtimerels(i) WHEN 0 THEN '</TD>'
          ELSE mtimerels(i) || '%</TD>' END) || CRLF ||
        '<TD ALIGN=LEFT>' || CRLF ||
        '<A HREF =" ' || filename || '_pc.html#' ||
        fnames(i) || '#' || hashtab(i) || '">' || fnames(i) ||
        '</A></TD></TR>' || CRLF,
        FALSE);
    END LOOP;

    write_data('</TABLE></PRE></BODY></HTML>', TRUE);
    write_plstinfo;
  END;

  /* Generate function level difference reports sorted on a particular
   * attribute.
   */
  PROCEDURE diff_function_level_report(ordertype PLS_INTEGER) IS
    orderclause VARCHAR2(100);
    sortedby    VARCHAR2(100);
    bstime      VARCHAR2(30) := '<td class="num">';
    bftime      VARCHAR2(30) := '<td class="num">';
    bmstime     VARCHAR2(30) := '<td class="num">';
    bmftime     VARCHAR2(30) := '<td class="num">';
    bcalls      VARCHAR2(30) := '<td class="num">';
    bname       VARCHAR2(30) := '<td class="str">';
    calls       NumList;
    callrels    NumList;
    fnames      NameList;
    stimes      NumList;
    ftimes      NumList;
    dtimes      NumList;
    mftimes     NumList2;
    ftimerels   NumList;
    mtimerels   NumList;
    ftimeind    NUMBER(38,1);

  BEGIN
    IF (ordertype = DBMSHP_SUBTREETIME) THEN
      orderclause := 'stime desc';
      sortedby := 'Total Subtree ';
      bstime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_FUNCTIONTIME) THEN
      orderclause := 'ftime desc';
      sortedby := 'Total Function ';
      bftime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_CALLS) THEN
      orderclause := 'calls desc';
      sortedby := 'Total Number of Calls ';
      bcalls := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_NAME) THEN
      orderclause := 'names asc';
      sortedby := 'Function Name ';
      bname := '<td class="bstr">';
    END IF;

    report_title('Function ' || time_mode || units || 'Data Sorted By ' ||
                 sortedby || time_mode || units || 'Delta for ' || filename);

    write_data(
      '<TABLE BORDER=1>' || CRLF || '<thead>' || CRLF ||
      '<TD>Subtree Time Delta</TD><TD>Function Time Delta</TD><TD>Rel%</TD>' ||
      '<TD>Ind%</TD><TD>Descendants Time Delta</TD><TD>Calls Delta</TD>' ||
      '<TD>Rel%</TD><TD>Mean Function Time Delta</TD><TD>Rel%</TD>' ||
      '<TD class="left">Function Name</TD></thead>' || CRLF,
      FALSE);

    sql_stmt := 'SELECT SUM(ftime) FROM dbmshp_diftab WHERE ftime > 0';
    EXECUTE IMMEDIATE sql_stmt INTO perf_time1;

    sql_stmt := 'SELECT SUM(ftime) FROM dbmshp_diftab WHERE ftime < 0';
    EXECUTE IMMEDIATE sql_stmt INTO perf_time2;

    sql_stmt :=
      'SELECT calls, callsrels, names, ftime, stime, (stime-ftime), ' ||
      'ftimerel, mftime, mftimerel FROM dbmshp_diftab ' ||
      'ORDER BY ' || orderclause;

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      calls, callrels, fnames, ftimes, stimes, dtimes, ftimerels,
      mftimes, mtimerels;

    FOR i in 1..fnames.COUNT LOOP

      IF (ftimes(i) < 0) THEN
        ftimeind := ftimes(i)/perf_time2*100;
      ELSE
        ftimeind := ftimes(i)/perf_time1*100;
      END IF;

      write_data(
        '<TR ALIGN=RIGHT>' || CRLF ||
        bstime    || stimes(i)    || '</TD>' || CRLF ||
        bftime    || ftimes(i)    || '</TD>' || CRLF ||
        bftime    || (CASE ftimerels(i) WHEN 0 THEN '</TD>'
          ELSE ftimerels(i) || '%</TD>' END) || CRLF ||
        bftime    || (CASE ftimeind WHEN 0 THEN '</TD>'
          ELSE ftimeind || '%</TD>' END) || CRLF ||
        '<TD>'    || dtimes(i)    || '</TD>' || CRLF ||
        bcalls    || calls(i)     || '</TD>' || CRLF ||
        bcalls    || (CASE callrels(i) WHEN 0 THEN '</TD>'
          ELSE callrels(i) || '%</TD>' END) || CRLF ||
        '<TD>'    || (CASE mftimes(i) WHEN 0 THEN '</TD>'
          ELSE mftimes(i)|| '</TD>' END) || CRLF ||
        '<TD>'    || (CASE mtimerels(i) WHEN 0 THEN '</TD>'
          ELSE mtimerels(i) || '%</TD>' END) || CRLF ||
        bname     || '<a class="name_pls"' || 
        '<A HREF =" ' || filename || '_pc.html#' ||
        fnames(i) || '">' || fnames(i) ||
        '</A></TD></TR>' || CRLF,
        FALSE);
    END LOOP;

    write_data(
      '</TABLE>' || CRLF || CRLF || '</BODY>' || CRLF || '</HTML>',
      TRUE);

    write_plstinfo;
  END;

  /* Generate namespace level difference report */
  PROCEDURE diff_namespace_report IS
    units       VARCHAR2(20) := ' (in microsecs) ';
    ftimes1     NumList;
    ftimes2     NumList;
    ftimes      NumList;
    calls       NumList;
    calls1      NumList;
    calls2      NumList;
    fnames      NameList;

  BEGIN

    report_title(time_mode||' Report' || units || 'Group By Namespace');

    write_data(
      '<table border="1">' || CRLF ||
      '<thead style="color:brown">' || CRLF ||
      '<td class="left" rowspan=3>Namespace</td>' || CRLF ||
      '<td class="left" ColSpan=3>' || time_mode || '</td>' || CRLF ||
      '<td class="left" ColSpan=3>Calls</td>' || CRLF ||
      '<TR>' || CRLF ||
      '<td class="left">First Trace</td>' || CRLF ||
      '<td class="left">Second Trace</td>' || CRLF ||
      '<td class="left">Delta</td>' || CRLF ||
      '<td class="left">First Trace</td>' || CRLF ||
      '<td class="left">Second Trace</td>' || CRLF ||
      '<td class="left">Delta</td>' || CRLF ||
      '</TR>' || CRLF ||
      '</thead>' || CRLF, FALSE);

    sql_stmt :=
      'SELECT sum(r1), sum(r2), sum(ftime), ' ||
      'sum(c1), sum(c2), sum(calls), namespace ' ||
      'FROM dbmshp_diftab GROUP BY namespace ' || 
      'ORDER BY namespace';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      ftimes1, ftimes2, ftimes, calls1, calls2, calls, fnames;

    FOR i in ftimes.first..ftimes.last LOOP
      write_data( 
        '<tr>' || CRLF || 
        '<td class="left"><b>' || fnames(i)  || '</b></td>' || CRLF ||
        '<td>' || ftimes1(i) || '</td>' || CRLF ||
        '<td>' || ftimes2(i) || '</td>' || CRLF ||
        '<td>' || ftimes(i)  || '</td>' || CRLF ||
        '<td>' || calls1(i)  || '</td>' || CRLF ||
        '<td>' || calls2(i)  || '</td>' || CRLF ||
        '<td>' || calls(i)   || '</td>' || CRLF ||
        '</tr>' || CRLF, FALSE);
    END LOOP;

    write_data('</table>' || CRLF || '</BODY>' || CRLF || '</HTML>', TRUE);
    write_plstinfo;
  END;

  /* Generate module level difference report */
  PROCEDURE diff_module_report(ordertype PLS_INTEGER) IS
    orderclause VARCHAR2(30);
    sortedby    VARCHAR2(30);
    bftime      VARCHAR2(30) := '<td class="num">';
    bcalls      VARCHAR2(30) := '<td class="num">';
    bname       VARCHAR2(30) := '<td class="str">';
    ftimes      NumList;
    fnames      NameList;
    calls       NumList;
    ftimes1     NumList;
    ftimes2     NumList;
    calls1      NumList;
    calls2      NumList;

  BEGIN

    IF (ordertype = DBMSHP_FUNCTIONTIME) THEN
      orderclause := 'delta desc';
      sortedby := 'Module Time' || units;
      bftime := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_CALLS) THEN
      orderclause := 'calls desc';
      sortedby := 'Call Count';
      bcalls := '<td class="bnum">';
    ELSIF (ordertype = DBMSHP_NAME) THEN
      orderclause := 'mname'; 
      sortedby := 'Module Name';
      bname := '<td class="bstr">';
    END IF;

    report_title('Module Level Difference Report - Sorted by ' || sortedby);

    write_data(
      '<table border="1">' || CRLF ||
      '<thead>' || CRLF ||
      '<td class="left" ColSpan=3>Module Time</td>' || CRLF ||
      '<td class="left" ColSpan=3>Calls</td>' || CRLF ||
      '<td class="left" rowspan=3>Module Name</td>' || CRLF ||
      '<TR>' || CRLF ||
      '<td class="left">First Trace</td>' || CRLF ||
      '<td class="left">Second Trace</td>' || CRLF ||
      '<td class="left">Delta</td>' || CRLF ||
      '<td class="left">First Trace</td>' || CRLF ||
      '<td class="left">Second Trace</td>' || CRLF ||
      '<td class="left">Delta</td>' || CRLF ||
      '</TR>' || CRLF ||
      '</thead>' || CRLF, FALSE);

    sql_stmt :=
      'SELECT sum(r1), sum(r2), sum(ftime) delta, ' ||
      'sum(c1), sum(c2), sum(calls) calls, mname ' ||
      'FROM dbmshp_diftab GROUP BY mname ' || 
      'ORDER BY ' || orderclause;

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      ftimes1, ftimes2, ftimes, calls1, calls2, calls, fnames;

    FOR i in ftimes.first..ftimes.last LOOP
      write_data(
        '<tr>' || CRLF ||
        '<td>' || ftimes1(i) || '</td>' || CRLF ||
        '<td>' || ftimes2(i) || '</td>' || CRLF ||
        bftime || ftimes(i)  || '</td>' || CRLF ||
        '<td>' || calls1(i)  || '</td>' || CRLF ||
        '<td>' || calls2(i)  || '</td>' || CRLF ||
        bcalls || calls(i)   || '</td>' || CRLF ||
        bname  || fnames(i)  || '</td>' || CRLF ||
        '</tr>' || CRLF, FALSE);
    END LOOP;

    write_data('</table>' || CRLF || '</BODY>' || CRLF || '</HTML>', TRUE);
    write_plstinfo;
  END;

  /* Generate parents and children difference report */
  PROCEDURE diff_parent_child_report IS
    fnames      NameList;
    pnames      NameList;
    cnames      NameList;
    stimes      NumList;
    ftimes      NumList;
    dtimes      NumList;
    mftimes     NumList2;
    calls       NumList;
    mtimerels   NumList;
    stimes1     NumList;
    stimes2     NumList;
    ftimes1     NumList;
    ftimes2     NumList;
    dtimes1     NumList;
    dtimes2     NumList;
    calls1      NumList;
    calls2      NumList;
    hashtab     RawList;
    hashtab2    RawList;
    ftimeind    NUMBER(38,1);
    stimeind    NUMBER(38,1);
    dtimeind    NUMBER(38,1);
    callsind    NUMBER(38,1);
    pstimes     NumList;
    pftimes     NumList;
    pcalls      NumList;
    sids        NumList;
    pids        NumList;
    cids        NumList;
    x           PLS_INTEGER;
    dtime       NUMBER;
    perform     VARCHAR2(50);
    fsum1       NUMBER(38,0);
    fsum2       NUMBER(38,0);
    csum1       NUMBER(38,0);
    csum2       NUMBER(38,0);
    stimeind1   NUMBER(38,1);
    stimeind2   NUMBER(38,1);
    ftimeind1   NUMBER(38,1);
    ftimeind2   NUMBER(38,1);
    dtimeind1   NUMBER(38,1);
    dtimeind2   NUMBER(38,1);
    callsind1   NUMBER(38,1);
    callsind2   NUMBER(38,1);
    mftime1     NUMBER(38,1);
    mftime2     NUMBER(38,1);
    mstime      NUMBER(38,1);
    mstime1     NUMBER(38,1);
    mstime2     NUMBER(38,1);
    mstimeind   NUMBER(38,1);
    mdtime      NUMBER(38,1);
    mdtime1     NUMBER(38,1);
    mdtime2     NUMBER(38,1);
    mdtimeind   NUMBER(38,1);

  BEGIN

    -- dbmshp_pct1 view contains parent-child level profiler information from
    -- dbmshp_parent_child_info and dbmshp_function_info tables for a
    -- particular run specified by runid.
    sql_stmt :=
      'CREATE OR REPLACE VIEW dbmshp_pct1 AS SELECT ' ||
      'p.parentsymid pid, p.childsymid cid, ' ||
      'p.subtree_elapsed_time stime, p.function_elapsed_time ftime, ' ||
      'p.calls, f.namespace, f.hash, f.line#, ' ||
      'x.hash phash, x.namespace pnamespace, x.line# pline, ' ||
      'SUBSTR(decode(f.owner, null, '''', f.owner||''.'')||' ||
      'decode(f.module, null, '''', f.module||''.'')||' ||
      'decode(f.function,null, '''', f.function),1,100) name, ' ||
      'SUBSTR(decode(x.owner, null, '''', x.owner||''.'')||' ||
      'decode(x.module, null, '''', x.module||''.'')||' ||
      'decode(x.function,null, '''', x.function),1,100) pname ' ||
      'FROM dbmshp_parent_child_info p, dbmshp_function_info f, ' ||
      '(select * from dbmshp_function_info) x ' ||
      'WHERE f.runid = ' || runid || ' AND p.runid = ' || runid ||
      ' AND x.runid = ' || runid ||
      ' AND p.childsymid = f.symbolid' ||
      ' AND p.parentsymid = x.symbolid';
    EXECUTE IMMEDIATE sql_stmt;

    -- dbmshp_pct2 view contains parent-child level profiler information from
    -- dbmshp_parent_child_info and dbmshp_function_info tables for a
    -- particular run specified by runid2.
    sql_stmt :=
      'CREATE OR REPLACE VIEW dbmshp_pct2 AS SELECT ' ||
      'p.parentsymid pid, p.childsymid cid, ' ||
      'p.subtree_elapsed_time stime, p.function_elapsed_time ftime, ' ||
      'p.calls, f.namespace, f.hash, f.line#, ' ||
      'x.hash phash, x.namespace pnamespace, x.line# pline, ' ||
      'SUBSTR(decode(f.owner, null, '''', f.owner||''.'')||' ||
      'decode(f.module, null, '''', f.module||''.'')||' ||
      'decode(f.function,null, '''', f.function),1,100) name, ' ||
      'SUBSTR(decode(x.owner, null, '''', x.owner||''.'')||' ||
      'decode(x.module, null, '''', x.module||''.'')||' ||
      'decode(x.function,null, '''', x.function),1,100) pname ' ||
      'FROM dbmshp_parent_child_info p, dbmshp_function_info f, ' ||
      '(select * from dbmshp_function_info) x ' ||
      'WHERE f.runid = ' || runid2 || ' AND p.runid = ' || runid2 ||
      ' AND x.runid = ' || runid2 ||
      ' AND p.childsymid = f.symbolid' ||
      ' AND p.parentsymid = x.symbolid';
    EXECUTE IMMEDIATE sql_stmt;

    -- dbmshp_pcdiftab view contains parent-child level profiler differences
    -- information from dbmshp_pct1 and dbmshp_pct2 views.
    sql_stmt :=
      'CREATE OR REPLACE VIEW dbmshp_pcdiftab AS SELECT ' ||
      'dbmshp_pct1.pid pid, dbmshp_pct1.cid cid, ' ||
      '(dbmshp_pct2.stime-dbmshp_pct1.stime) stime,' ||
      '(dbmshp_pct2.ftime-dbmshp_pct1.ftime) ftime, ' ||
      '(dbmshp_pct2.calls-dbmshp_pct1.calls) calls, ' ||
      'dbmshp_pct1.namespace, dbmshp_pct1.hash, ' ||
      'dbmshp_pct1.pname||decode(dbmshp_pct1.pline,0,'''',' ||
      '''(Line ''||dbmshp_pct1.pline||'')'') pname, dbmshp_pct1.phash, ' ||
      'dbmshp_pct1.name||decode(dbmshp_pct1.line#,0,'''',' ||
      '''(Line ''||dbmshp_pct1.line#||'')'') ' ||
      'names FROM dbmshp_pct1, dbmshp_pct2 WHERE ' ||
      '(substr(dbmshp_pct1.name||dbmshp_pct1.hash,1,100) = ' ||
      'substr(dbmshp_pct2.name||dbmshp_pct2.hash,1,100)) AND ' ||
      '(dbmshp_pct1.namespace = dbmshp_pct2.namespace) AND ' ||
      '(substr(dbmshp_pct1.pname||dbmshp_pct1.phash,1,100) = ' ||
      'substr(dbmshp_pct2.pname||dbmshp_pct2.phash,1,100)) AND ' ||
      '(dbmshp_pct1.pnamespace = dbmshp_pct2.pnamespace) ' ||
      'UNION SELECT ' ||
      'pid, cid, stime, ftime, calls, namespace, hash, ' ||
      'pname||decode(pline,0,'''',''(Line ''||pline||'')'') ' ||
      'pname, phash, ' ||
      'name||decode(line#,0,'''',''(Line ''||line#||'')'') ' ||
      'names FROM dbmshp_pct2 ' ||
      'WHERE name NOT IN (SELECT name from dbmshp_pct1) ' ||
      'OR pname NOT IN (SELECT pname from dbmshp_pct1) ' ||
      'UNION SELECT ' ||
      'pid, cid, -stime, -ftime, -calls, namespace, hash, ' ||
      'pname||decode(pline,0,'''',''(Line ''||pline||'')'') ' ||
      'pname, phash, ' ||
      'name||decode(line#,0,'''',''(Line ''||line#||'')'') ' ||
      'names FROM dbmshp_pct1 ' ||
      'WHERE name NOT IN (SELECT name from dbmshp_pct2) ' ||
      'OR pname NOT IN (SELECT pname from dbmshp_pct2)';

    EXECUTE IMMEDIATE sql_stmt;

    report_title('Parents and Children Difference Report with ' ||
                 time_mode || units);

    sql_stmt := 'SELECT SUM(ftime), SUM(calls) FROM dbmshp_t1';
    EXECUTE IMMEDIATE sql_stmt INTO fsum1, csum1;

    sql_stmt := 'SELECT SUM(ftime), SUM(calls) FROM dbmshp_t2';
    EXECUTE IMMEDIATE sql_stmt INTO fsum2, csum2;

    sql_stmt :=
      'SELECT calls, names, hash, ftime, stime, sid, (stime-ftime), ' ||
      'mftime, mftimerel, c1, c2, r1, r2, s1, s2, d1, d2 ' ||
      'FROM dbmshp_diftab ORDER BY sid';

    EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
      calls, fnames, hashtab, ftimes, stimes, sids, dtimes, mftimes, mtimerels,
      calls1, calls2, ftimes1, ftimes2, stimes1, stimes2, dtimes1, dtimes2;

    FOR i in 1..sids.COUNT LOOP
      IF (ftimes(i) < 0) THEN
        ftimeind := ftimes(i)/perf_time2*100;
        perform := '('  || ftimeind || '% of total improvement)';
      ELSIF (ftimes(i) > 0) THEN
        ftimeind := ftimes(i)/perf_time1*100;
        perform := '(' || ftimeind || '% of total regression)';
      ELSE
        perform := '';
      END IF;

      IF ftimes1(i) = 0 THEN
        ftimeind := 0;
      ELSE
        ftimeind  :=  ftimes(i)/ftimes1(i)*100;
      END IF;
      ftimeind1 := ftimes1(i)/fsum1*100;
      ftimeind2 := ftimes2(i)/fsum2*100;

      IF stimes1(i) = 0 THEN
        stimeind := 0;
      ELSE
        stimeind  :=  stimes(i)/stimes1(i)*100;
      END IF;
      stimeind1 := stimes1(i)/fsum1*100;
      stimeind2 := stimes2(i)/fsum2*100;

      IF dtimes1(i) = 0 THEN
        dtimeind := 0;
      ELSE
        dtimeind  :=  dtimes(i)/dtimes1(i)*100;
      END IF;
      dtimeind1 := dtimes1(i)/fsum1*100;
      dtimeind2 := dtimes2(i)/fsum2*100;

      IF calls1(i) = 0 THEN
        callsind := 0;
        mftime1 := 0;
        mstime1 := 0;
        mdtime1 := 0;
      ELSE
        callsind :=  calls(i)/calls1(i)*100;
        mftime1 := ftimes1(i)/calls1(i);
        mstime1 := stimes1(i)/calls1(i);
        mdtime1 := dtimes1(i)/calls1(i);
      END IF;
      callsind1 := calls1(i)/csum1*100;
      callsind2 := calls2(i)/csum2*100;

      IF calls2(i) = 0 THEN
        mftime2 := 0;
        mstime2 := 0;
        mdtime2 := 0;
      ELSE
        mftime2 := ftimes2(i)/calls2(i);
        mstime2 := stimes2(i)/calls2(i);
        mdtime2 := dtimes2(i)/calls2(i);
      END IF;

      mstime := mstime2-mstime1;
      mdtime := mdtime2-mdtime1;

      IF mstime1 = 0 THEN
        mstimeind := 0;
      ELSE
        mstimeind := mstime/mstime1*100;
      END IF;

      IF mdtime1 = 0 THEN
        mdtimeind := 0;
      ELSE
        mdtimeind := mdtime/mdtime1*100;
      END IF;

      write_data(
      '<H3>Comparision for <A NAME ="' || fnames(i) || '#' || hashtab(i) ||
      '">' || fnames(i) || '</A>' || perform ||
      '</H3>' || CRLF ||
      '<TABLE BORDER=1>' || CRLF ||
      '<TR><TD CLASS="left" rowspan=2>' || fnames(i) || '</TD>' ||
      '<TD ColSpan=2>First Trace</TD>' ||
      '<TD ColSpan=2>Second Trace</TD>' || 
      '<TD rowspan=2>Diff</TD><TD rowspan=2>Diff%</TD></TR>' ||
      '<TR>' || CRLF ||
      '<TD>Measure</TD><TD>Ind%</TD>' || CRLF ||
      '<TD>Measure</TD><TD>Ind%</TD>' || CRLF ||
      '</TR>' || CRLF ||
      (CASE (ftimes1(i)+ftimes2(i)) WHEN 0 THEN '' ELSE
      '<TR><TD CLASS="LEFT">' ||
      'Function ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || (CASE ftimes1(i) WHEN 0 THEN '</TD><TD></TD>' ELSE
         ftimes1(i) || '</TD><TD>' || ftimeind1 || '%</TD>' END) ||
      '<TD>' || (CASE ftimes2(i) WHEN 0 THEN '</TD><TD></TD>' ELSE
         ftimes2(i) || '</TD><TD>' || ftimeind2 || '%</TD>' END) ||
      '<TD>' || ftimes(i)  || '</TD><TD>' || ftimeind  || '%</TD>' || CRLF ||
      '</TR>' || CRLF END) ||
      (CASE dtimes1(i) WHEN 0 THEN '' ELSE
      '<TR><TD CLASS="LEFT">' ||
      'Descendants ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || dtimes1(i) || '</TD><TD>' || dtimeind1 || '%</TD>' ||
      '<TD>' || dtimes2(i) || '</TD><TD>' || dtimeind2 || '%</TD>' ||
      '<TD>' || dtimes(i)  || '</TD><TD>' || dtimeind  || '%</TD>' || CRLF ||
      '</TR>' || CRLF ||
      '<TR><TD CLASS="LEFT">' ||
      'Subtree ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || stimes1(i) || '</TD><TD>' || stimeind1 || '%</TD>' ||
      '<TD>' || stimes2(i) || '</TD><TD>' || stimeind2 || '%</TD>' ||
      '<TD>' || stimes(i)  || '</TD><TD>' || stimeind  || '%</TD>' || CRLF ||
      '</TR>' || CRLF END) ||
      '<TR><TD CLASS="LEFT">' ||
      'Function Calls</TD>' ||
      '<TD>' || (CASE calls1(i) WHEN 0 THEN '</TD><TD></TD>' ELSE
        calls1(i) || '</TD><TD>' || callsind1 || '%</TD>' END) ||
      '<TD>' || (CASE calls2(i) WHEN 0 THEN '</TD><TD></TD>' ELSE
        calls2(i) || '</TD><TD>' || callsind2 || '%</TD>' END) ||
      '<TD>' || calls(i)  || '</TD><TD>' || callsind  || '%</TD>' || CRLF ||
      '</TR>' || CRLF ||
      (CASE (mftime1+mftime2) WHEN 0 THEN '' ELSE
      '<TR><TD CLASS="LEFT">' ||
      'Mean Function ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || (CASE mftime1 WHEN 0 THEN '</TD>' ELSE
        mftime1 || '</TD>' END) || '<TD></TD>' ||
      '<TD>' || (CASE mftime2 WHEN 0 THEN '</TD>' ELSE
        mftime2 || '</TD>' END) || '<TD></TD>' ||
      '<TD>' || (CASE mftimes(i) WHEN 0 THEN '</TD>' ELSE
        mftimes(i) || '</TD>' END) ||
      '<TD>' || (CASE mtimerels(i) WHEN 0 THEN '</TD>' ELSE
        mtimerels(i) || '%</TD>' END) || CRLF ||
      '</TR>' || CRLF END) ||
      (CASE mdtime1 WHEN 0 THEN '' ELSE
      '<TR><TD CLASS="LEFT">' ||
      'Mean Descendants ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || mdtime1     || '</TD><TD></TD>' ||
      '<TD>' || mdtime2     || '</TD><TD></TD>' ||
      '<TD>' || mdtime      || '</TD><TD>' || mdtimeind || '%</TD>' || CRLF ||
      '</TR>' || CRLF ||
      '<TR><TD CLASS="LEFT">' ||
      'Mean Subtree ' || time_mode || ' (microsecs)s</TD>' ||
      '<TD>' || mstime1     || '</TD><TD></TD>' ||
      '<TD>' || mstime2     || '</TD><TD></TD>' ||
      '<TD>' || mstime      || '</TD><TD>' || mstimeind || '%</TD>' || CRLF ||
      '</TR>' || CRLF END) || '</TABLE><BR/>' || CRLF,
       FALSE);

      -- Output name
      write_data('<b>' || fnames(i) || '</b>' || CRLF, FALSE);

      -- Output table
      write_data(
        '<TABLE BORDER="1">' || CRLF ||
        '<TR ALIGN=RIGHT>' ||
        '  <td>Subtree Time Delta</td>' || CRLF ||
        '  <td>Function Time Delta</td>' || CRLF ||
        '  <td>Descendants Time Delta</td>' || CRLF ||
        '  <td>Calls Delta</td>' || CRLF ||
        '  <td class="left">Function Name</td>' || CRLF ||
        '</TR>'|| CRLF ||
        '<TR ALIGN=RIGHT>' || CRLF ||
        ' <td>' || stimes(i) || '</td>' || CRLF ||
        ' <td>' || ftimes(i) || '</td>' || CRLF ||
        ' <td>' || dtimes(i) || '</td>' || CRLF ||
        ' <td>' || calls(i)  || '</td>' || CRLF ||
        ' <td class="left">' || CRLF ||
        '   <A HREF="'  || filename || '_pc.html#' || fnames(i) ||
        '#' || hashtab(i) ||
        '">' || fnames(i) || '</A>' || CRLF || ' </td>' || CRLF ||
        '</TR>' || CRLF, FALSE);

      -- output Parent info.
      sql_stmt := 'SELECT pid, stime, ftime, calls, pname, phash ' ||
        'FROM dbmshp_pcdiftab WHERE names||hash = :b1 and cid = :b2 ' ||
        'ORDER BY stime desc';

      EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO
        pids, pstimes, pftimes, pcalls, pnames, hashtab2
        USING fnames(i)||hashtab(i), sids(i);

      write_data(
        '<tr>' || CRLF ||
        ' <td>' || CRLF ||
        '   <b>Parents:</b>' || CRLF ||
        ' </td>' || CRLF ||
        '</tr>' || CRLF, FALSE);

      -- <ROOT> entry
      IF (pids.COUNT = 0) THEN
        write_data(
          '<tr>' || CRLF ||
          ' <td>' || stimes(i) || '</td>' || CRLF ||
          ' <td>' || ftimes(i) || '</td>' || CRLF ||
          ' <td>' || dtimes(i) || '</td>' || CRLF ||
          ' <td>' || calls(i)  || '</td>' || CRLF ||
          ' <td class="left">' || CRLF ||
          '   <A HREF="' || filename ||
          '_pc.html#root">root</A>' || CRLF ||
          ' </td>' || CRLF ||
          '</tr>' || CRLF, FALSE);
      END IF;

      FOR j in 1..pnames.COUNT LOOP
        IF (pstimes(j) <> 0 OR pftimes(j) <> 0) THEN
          dtime := pstimes(j)-pftimes(j);

          write_data(
            '<tr>' || CRLF ||
            ' <td>' || pstimes(j) || '</td>' || CRLF ||
            ' <td>' || pftimes(j) || '</td>' || CRLF ||
            ' <td>' || dtime      || '</td>' || CRLF ||
            ' <td>' || pcalls(j)  || '</td>' || CRLF ||
            ' <td class="left">'  || CRLF ||
            '   <A HREF="'  || filename || '_pc.html#' || pnames(j) ||
            '#' || hashtab2(j) ||
            '">' || pnames(j) || '</A>' || CRLF || ' </td>' || CRLF ||
            '</tr>' || CRLF, FALSE);
        END IF;
      END LOOP;

      -- output children info.
      sql_stmt := 'SELECT cid, stime, ftime, calls, names, hash ' ||
        'FROM dbmshp_pcdiftab WHERE pname||phash = :b1 and pid = :b2 ' ||
        'order by stime asc';

      EXECUTE IMMEDIATE sql_stmt BULK COLLECT
        INTO cids, pstimes, pftimes, pcalls, cnames, hashtab2
        USING fnames(i)||hashtab(i), sids(i);

      IF cids.COUNT > 0 THEN
        write_data('<tr><td><b>Children:</b></td></tr>' || CRLF, FALSE);
      END IF;

      FOR j in 1..cids.COUNT LOOP
        IF (pstimes(j) <> 0 OR pftimes(j) <> 0) THEN
          dtime := pstimes(j)-pftimes(j);

          write_data(
            '<tr>' || CRLF ||
            '<td>' || pstimes(j) || '</td>' || CRLF ||
            '<td>' || pftimes(j) || '</td>' || CRLF ||
            '<td>' || dtime     || '</td>' || CRLF ||
            '<td>' || pcalls(j)  || '</td>' || CRLF ||
            '<td class="left">' || CRLF ||
            '  <A HREF="' || filename || '_pc.html#' || cnames(j) ||
            '#' || hashtab2(j) ||
            '">' || cnames(j) || '</A>' || CRLF || ' </td>' || CRLF ||
            '</tr>' || CRLF, FALSE);
        END IF;
      END LOOP;

      write_data('</table><br/>'|| CRLF, FALSE);
    END LOOP;

    write_data(
      '</BODY>' || CRLF || '</HTML>',
      TRUE);
    write_plstinfo;
  END;

  /* analyze_reports takes a location, a file name and a run_id
   * and generates a report based on data collected in the trace
   * file <fname> located at directory object <location> with run_id
   * <run_id>.
   *
   * The report is composed of several HTML files including:
   * 1. A time analysis report
   * 2. A subtree time report
   * 3. A function time report
   * 4. A function call count report
   * 5. A function name report
   * 6. A mean subtree time report
   * 7. A mean function time report
   * 8. A group by namespace report
   * 9. A group by module name report
   * 10.A total function call count report
   * 11.A module name report
   * 12.A parent child diff report
   *
   * There are many other potential reports that one could wish
   * to generate. These reports and their implementations demonstrate
   * ways of leveraging the trace information left in the PL/SQL trace
   * files.
   */
  PROCEDURE analyze_reports(location      IN VARCHAR2,
                            fname         IN VARCHAR2,
                            run_id        IN NUMBER) IS
  BEGIN

    filename     := fname;
    filelocation := location;
    runid        := run_id;

    -- Get total number of time
    time_mode := 'Elapsed Time';
    sql_stmt :=
      'SELECT SUM(FUNCTION_ELAPSED_TIME) FROM dbmshp_function_info ' ||
        'WHERE runid = :b1';
    EXECUTE IMMEDIATE sql_stmt INTO total_time USING runid;

    -- Get total number of function calls
    sql_stmt := 'SELECT SUM(CALLS) FROM dbmshp_function_info ' ||
     'WHERE runid = :b1';
    EXECUTE IMMEDIATE sql_stmt INTO total_calls USING runid;

    -- Generate PL/SQL Time Analysis report
    fullname := filename||'.html';
    single_run_start_page;

    -- Generate Sort by Subtree Time report
    fullname := filename||'_subtree.html';
    function_level_report(DBMSHP_SUBTREETIME, FALSE);

    -- Generate Sort by Function Time report
    fullname := filename||'_function.html';
    function_level_report(DBMSHP_FUNCTIONTIME, FALSE);

    -- Generate Sort by Function Call Count report
    fullname := filename||'_calls.html';
    function_level_report(DBMSHP_CALLS, FALSE);

    -- Generate Sort by Function Name report
    fullname := filename||'_name.html';
    function_level_report(DBMSHP_NAME, FALSE);

    -- Generate Sort by Mean Subtree Time report
    fullname := filename||'_mean_subtree.html';
    function_level_report(DBMSHP_MEAN_SUBTREETIME, TRUE);

    -- Generate Sort by Mean Function Time report
    fullname := filename||'_mean_function.html';
    function_level_report(DBMSHP_MEAN_FUNCTIONTIME, TRUE);

    -- Generate Group by Namespace report
    fullname := filename||'_namespace.html';
    namespace_report;

    -- Generate Group by Module Name reports
    -- Sorted by Total Function Time
    fullname := filename||'_module_function.html';
    module_report(DBMSHP_FUNCTIONTIME);

    -- Sorted by Total Function Call Count
    fullname := filename||'_module_calls.html';
    module_report(DBMSHP_CALLS);

    -- Sorted by Module Name
    fullname := filename||'_module_name.html';
    module_report(DBMSHP_NAME);

    -- Generate Parent Child Time report
    fullname := filename||'_parent_child.html';
    parent_child_report;
  END;

  PROCEDURE analyze_reports(location      IN VARCHAR2,
                            fname         IN VARCHAR2,
                            run1_id       IN NUMBER,
                            run2_id       IN NUMBER) IS
  BEGIN

    filename     := fname;
    filelocation := location;
    runid        := run1_id;
    runid2       := run2_id;
  
    time_mode := 'Elapsed Time';

    -- Generate PL/SQL Time Diff Summary report
    fullname := filename || '.html';

    IF (diff_mode_start_page = TRUE) THEN
      -- Generate performance regression report
      fullname := filename || '_fr.html';
      performance_report('ftime > 0', 'Regressions');

      -- Generate performance improvement report
      fullname := filename || '_fi.html';
      performance_report('ftime < 0', 'Improvements');

      -- Generate Sort by Total Subtree Time report
      fullname := filename || '_ns.html';
      diff_function_level_report(DBMSHP_SUBTREETIME);

      -- Generate Sort by Total Function Time report
      fullname := filename || '_nf.html';
      diff_function_level_report(DBMSHP_FUNCTIONTIME);

      -- Generate Sort by Function Call Count report
      fullname := filename || '_nc.html';
      diff_function_level_report(DBMSHP_CALLS);

      -- Generate Sort by Function Name report
      fullname := filename || '_nn.html';
      diff_function_level_report(DBMSHP_NAME);

      -- Generate Group by Namespace reports
      fullname := filename || '_nsp.html';
      diff_namespace_report;

      -- Generate Group by Module Name reports
      -- Sorted by Total Function Time
      fullname := filename || '_2f.html';
      diff_module_report(DBMSHP_FUNCTIONTIME);

      -- Sorted by Total Function Call Count
      fullname := filename || '_2c.html';
      diff_module_report(DBMSHP_CALLS);

      -- Sorted by Module Name
      fullname := filename || '_2n.html';
      diff_module_report(DBMSHP_NAME);

      -- Generate Parent Child Difference reports
      fullname := filename || '_pc.html';
      diff_parent_child_report;

      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_t1';
      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_t2';
      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_diftab';
      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_pct1';
      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_pct2';
      EXECUTE IMMEDIATE 'DROP VIEW dbmshp_pcdiftab';
    END IF;
  END;

BEGIN
  dbms_lob.createtemporary(myclob, true);
  page_size := dbms_lob.getchunksize(myclob);
    
  -- Write a multiple of the page size that is closest to 32K
  IF (page_size < 32767) THEN
    page_size := 32767 / page_size * page_size;
  END IF;

END dbmshpro_demo;
/
show errors

